﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using OperationPlugins.ExtensionMethods;

namespace TimeProject.Engine.DbModels
{
    //[From(Table = "User", Alias = "U")]
    //[InnerJoin(Table = "UserActivity", TableAlias = "A", LeftAlias = "U", LeftColumn = "Id", RightAlias = "A", RightColumn = "UserId")]
    //public class MyCustomQuery : AdHocBase
    //{
    //    [Parameter(Name = "@p1")]
    //    [Column(Name = "Id", TableAlias = "U")]
    //    public int UserId { get; set; }

    //    [Column(Name = "Username", TableAlias = "U")]
    //    public string Username { get; set; }

    //    [Column(Name = "FullName", TableAlias = "U")]
    //    public string FullName { get; set; }

    //    [Column(Name = "Type", TableAlias = "A")]
    //    public int Type { get; set; }

    //    [Column(Name = "Created", TableAlias = "A")]
    //    public DateTime Created { get; set; }

    //    public MyCustomQuery()
    //    {
    //        WhereClause = "U.Id = @p1";
    //    }
    //}

    public abstract class AdHocBase
    {
        [AttributeUsage(AttributeTargets.Property, AllowMultiple = false)]
        public sealed class ColumnAttribute : Attribute
        {
            public string Name { get; set; }
            public string TableAlias { get; set; }            
        }

        [AttributeUsage(AttributeTargets.Property, AllowMultiple = false)]
        public sealed class ParameterAttribute : Attribute
        {
            public string Name { get; set; }            
        }

        [AttributeUsage(AttributeTargets.Class, AllowMultiple = false)]
        public sealed class FromAttribute : Attribute
        {
            public string Table { get; set; }
            public string Alias { get; set; }
        }

        [AttributeUsage(AttributeTargets.Class, AllowMultiple = true)]
        public sealed class InnerJoinAttribute : Attribute
        {
            public int Order { get; set; }
            public string Table { get; set; }
            public string TableAlias { get; set; }
            public string LeftAlias { get; set; }
            public string LeftColumn { get; set; }
            public string RightAlias { get; set; }
            public string RightColumn { get; set; }
        }

        protected string WhereClause { get; set; }

        public virtual string GetSql()
        {
            var aliases = new List<string>();

            var sql = new StringBuilder();

            sql.Append("SELECT ");

            var firstColumn = true;
            foreach (PropertyInfo property in GetType().GetPublicProperties())
            {
                var column = property.GetAttribute<ColumnAttribute>();
                if (column == null)
                {
                    continue;
                }

                if (firstColumn)
                {
                    sql.Append(string.Format("{0}.[{1}] AS {2}", column.TableAlias, column.Name, property.Name));
                }
                else
                {
                    sql.Append(string.Format(", {0}.[{1}] AS {2}", column.TableAlias, column.Name, property.Name));                    
                }

                firstColumn = false;
            }

            var from = GetType().GetAttribute<FromAttribute>();            
            sql.Append(string.Format(" FROM [{0}] {1}", from.Table, from.Alias));

            if (aliases.Contains(from.Alias) == false)
            {
                aliases.Add(from.Alias);
            }

            var innerJoins = GetType().GetAttributes<InnerJoinAttribute>().ToList();
            innerJoins.Sort((j1, j2) => j1.Order.CompareTo(j2.Order));

            foreach (InnerJoinAttribute join in innerJoins)
            {
                sql.Append(string.Format(" INNER JOIN [{0}] {1} ON {2}.{3} = {4}.{5}", 
                                        join.Table, 
                                        join.TableAlias, 
                                        join.LeftAlias, 
                                        join.LeftColumn, 
                                        join.RightAlias,
                                        join.RightColumn));

                if (aliases.Contains(join.TableAlias) == false)
                {
                    aliases.Add(join.TableAlias);
                }

                if (aliases.Contains(join.LeftAlias) == false)
                {
                    aliases.Add(join.LeftAlias);
                }

                if (aliases.Contains(join.RightAlias) == false)
                {
                    aliases.Add(join.RightAlias);
                }
            }

            sql.Append(" WHERE");
            bool firstWhere = true;
            foreach (string alias in aliases)
            {
                if (firstWhere)
                {
                    sql.Append(string.Format(" {0}.EntityStatus = 0", alias));                    
                }
                else
                {
                    sql.Append(string.Format(" AND {0}.EntityStatus = 0", alias));                                        
                }

                firstWhere = false;
            }

            if (string.IsNullOrEmpty(WhereClause) == false)
            {
                sql.Append(string.Format(" AND ({0})", WhereClause));
            }

            string s = sql.ToString();
            return s;
        }

        public virtual SqlParameter[] GetParameters()
        {
            var parameters = new List<SqlParameter>();

            foreach (PropertyInfo property in GetType().GetPublicProperties())
            {
                var p = property.GetAttribute<ParameterAttribute>();
                if (p == null)
                {
                    continue;
                }

                var parameter = new SqlParameter(p.Name, property.GetValue(this));
                parameters.Add(parameter);
            }

            return parameters.ToArray();
        }
    }
}
